package org.uninova.mobis.gtfs.db;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;

import org.uninova.mobis.pojos.Agency;
import org.uninova.mobis.pojos.Calendar;
import org.uninova.mobis.pojos.CalendarDate;
import org.uninova.mobis.pojos.FeedInfo;
import org.uninova.mobis.pojos.Route;
import org.uninova.mobis.pojos.Stop;
import org.uninova.mobis.pojos.StopTime;
import org.uninova.mobis.pojos.Transfer;
import org.uninova.mobis.pojos.Trip;
import org.uninova.mobis.utils.DBUtilsImpl;

public class GTFSDatabaseInteraction {
	
	public GTFSDatabaseInteraction() {}
	
	public int insertRequiredFieldsAgency(Connection conn, Agency agency) {
		DBUtilsImpl dbUtils = new DBUtilsImpl() ;
		
		String query = "INSERT INTO mobis_schema.agency (" +
				"agency_gtfs_id, " +
				"agency_name, " +
				"agency_url, " +
				"agency_lang, " +
				"agency_timezone) " +
				"VALUES (" + 
				agency.getAgencyId() + ",'" +
				agency.getAgencyName() + "','" + 
				agency.getAgencyUrl() + "','" + 
				agency.getAgencyLang() + "','" + 
				agency.getAgencyTimezone() + "');" ; 
		
		return dbUtils.modifyDB(conn, query) ;
	} 
	
	public int insertAllFieldsAgency(Connection conn, Agency agency) {
		DBUtilsImpl dbUtils = new DBUtilsImpl() ;
		
		String query = "INSERT INTO mobis_schema.agency (" +
				"agency_gtfs_id, " +
				"agency_name, " +
				"agency_url, " +
				"agency_lang, " +
				"agency_timezone, " +
				"agency_phone, " +
				"agency_fare_url) " +
				"VALUES (" +
				agency.getAgencyId() + ",'" + 
				agency.getAgencyName() + "','" + 
				agency.getAgencyUrl() + "','" + 
				agency.getAgencyLang() + "','" + 
				agency.getAgencyTimezone() + "','" + 
				agency.getAgencyPhone() + "','" + 
				agency.getAgencyFareUrl() + "');" ; 
		
		return dbUtils.modifyDB(conn, query) ;
	} 
	
	public int insertRequiredFieldsRoute(Connection conn, Route route) {
		DBUtilsImpl dbUtils = new DBUtilsImpl() ;
		
		String query = "INSERT INTO mobis_schema.route (" +
				"route_gtfs_id, " +
				"route_short_name, " +
				"route_long_name, " +
				"route_type) " +
				"VALUES ('" +
				route.getRouteId() + "','" + 
				route.getRouteShortName() + "','" + 
				route.getRouteLongName() + "'," + 
				route.getRouteType() + ");" ; 
		
		return dbUtils.modifyDB(conn, query) ;
	} 
	
	public int insertAllFieldsRoute(Connection conn, Route route) {
		DBUtilsImpl dbUtils = new DBUtilsImpl() ;
		
		String query = "INSERT INTO mobis_schema.route (" +
				"route_gtfs_id, " +
				"agency_gtfs_id, " +
				"route_short_name, " +
				"route_long_name, " +
				"route_desc, " +
				"route_type, " +
				"route_url, " +
				"route_color, " +
				"route_text_color) " +
				"VALUES ('" +
				route.getRouteId() + "'," + 
				route.getAgencyId() + ",'" + 
				route.getRouteShortName() + "','" + 
				route.getRouteLongName() + "','" + 
				route.getRouteDesc() + "'," + 
				route.getRouteType() + ",'" + 
				route.getRouteUrl() + "','" + 
				route.getRouteColor() + "','" + 
				route.getRouteTextColor() + "');" ; 
		
		return dbUtils.modifyDB(conn, query) ;
	}

	public int insertRequiredFieldsStop(Connection conn, Stop stop) {
		DBUtilsImpl dbUtils = new DBUtilsImpl() ;
		
		String query = "INSERT INTO mobis_schema.stop (" +
				"stop_gtfs_id, " +
				"stop_name, " +
				"stop_lat, " +
				"stop_lon) " +
				"VALUES (" +
				stop.getStopId() + ",'" + 
				stop.getStopName() + "'," + 
				stop.getStopLat() + "," + 
				stop.getStopLon() + ");" ; 
		
		return dbUtils.modifyDB(conn, query) ;
	} 
	
	public int insertAllFieldsStop(Connection conn, Stop stop) {
		DBUtilsImpl dbUtils = new DBUtilsImpl() ;
		
		String query = "INSERT INTO mobis_schema.stop (" +
				"stop_gtfs_id, " +
				"stop_code, " +
				"stop_name, " +
				"stop_desc, " +
				"stop_lat, " +
				"stop_lon, " +
				"zone_id, " +
				"stop_url, " +
				"location_type, " +
				"parent_station, " +
				"stop_timezone, " +
				"wheelchair_boarding) " +
				"VALUES (" +
				stop.getStopId() + ",'" + 
				stop.getStopCode() + "','" +
				stop.getStopName() + "','" +
				stop.getStopDesc() + "'," +
				stop.getStopLat() + "," + 
				stop.getStopLon() + "," + 
				stop.getZoneId() + ",'" + 
				stop.getStopUrl() + "'," + 
				stop.getLocationType() + "," + 
				stop.getParentStation() + ",'" + 
				stop.getStopTimezone() + "'," + 
				stop.getWheelchairBoarding() + ");" ; 
		
		return dbUtils.modifyDB(conn, query) ;
	}
	
	public int insertRequiredFieldsTrip(Connection conn, Trip trip) {
		DBUtilsImpl dbUtils = new DBUtilsImpl() ;
		
		String query = "SELECT * FROM mobis_schema.calendar WHERE service_gtfs_id=" + trip.getServiceId() + ";" ;
		
		ResultSet set = dbUtils.queryDB(conn, query) ;
		
		try {
			if (set.next()) {
			
				query = "INSERT INTO mobis_schema.trip (" +
						"trip_gtfs_id, " +
						"route_gtfs_id, " +
						"service_gtfs_id) " +
						"VALUES (" +
						trip.getTripId() + ",'" + 
						trip.getRouteId() + "','" + 
						trip.getServiceId() + "');" ; 
				
				return dbUtils.modifyDB(conn, query) ;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return 1 ;
	} 
	
	public int insertAllFieldsTrip(Connection conn, Trip trip) {
		DBUtilsImpl dbUtils = new DBUtilsImpl() ;
		
		String query = "SELECT * FROM mobis_schema.calendar WHERE service_gtfs_id=" + trip.getServiceId() + ";" ;
		
		ResultSet set = dbUtils.queryDB(conn, query) ;
		
		try {
			if (set.next()) {
				query = "INSERT INTO mobis_schema.trip (" +
					"trip_gtfs_id, " +
					"trip_headsign, " +
					"trip_short_name, " +
					"direction_id, " +
					"block_id, " +
					"shape_id, " +
					"wheelchair_accessible, " +
					"route_gtfs_id, " +
					"service_gtfs_id) " +
					"VALUES (" +
					trip.getTripId() + ",'" + 
					trip.getTripHeadsign() + "','" +
					trip.getTripShortName() + "'," +
					trip.getDirectionId() + ",'" +
					trip.getBlockId() + "','" + 
					trip.getShapeId() + "'," + 
					trip.getWheelchairAccessible() + ",'" + 
					trip.getRouteId() + "','" + 
					trip.getServiceId() + "');" ; 
			
				return dbUtils.modifyDB(conn, query) ;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return 1 ;
	}
	
	public int insertRequiredFieldsStopTime(Connection conn, StopTime stopTime) {
		DBUtilsImpl dbUtils = new DBUtilsImpl() ;
		
		String query = "INSERT INTO mobis_schema.stop_time (" +
				"arrival_time, " +
				"departure_time, " +
				"stop_gtfs_id, " +
				"trip_gtfs_id, " +
				"stop_sequence, " +
				"stop_headsign, " +
				"pickup_type, " +
				"drop_off_type, " +
				"shape_dist_traveled) " +
				"VALUES (" +
				stopTime.getArrivalTime() + "," + 
				stopTime.getDepartureTime() + ",'" + 
				stopTime.getStopId() + "','" + 
				stopTime.getTripId() + "'," + 
				stopTime.getStopSequence() + ",'" + 
				stopTime.getStopHeadsign() + "'," + 
				stopTime.getPickupType() + "," + 
				stopTime.getDropOffType() + "," + 
				stopTime.getShapeDistTraveled() + ");" ; 
		
		return dbUtils.modifyDB(conn, query) ;
	} 
	
	public int insertAllFieldsStopTime(Connection conn, StopTime stopTime) {
		DBUtilsImpl dbUtils = new DBUtilsImpl() ;
		
		String query = "SELECT * FROM mobis_schema.trip WHERE trip_gtfs_id=" + stopTime.getTripId() + ";" ;
		
		ResultSet set = dbUtils.queryDB(conn, query) ;
		
		try {
			if (set.next()) {
			
				query = "INSERT INTO mobis_schema.stop_time (" +
						"arrival_time, " +
						"departure_time, " +
						"stop_gtfs_id, " +
						"trip_gtfs_id, " +
						"stop_sequence) " +
						"VALUES ('" +
						stopTime.getArrivalTime() + "','" + 
						stopTime.getDepartureTime() + "','" + 
						stopTime.getStopId() + "','" + 
						stopTime.getTripId() + "'," + 
						stopTime.getStopSequence() + ")" ; 
				
				return dbUtils.modifyDB(conn, query) ;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return 1 ;
	}
	
	public int insertAllFieldsCalendar(Connection conn, Calendar calendar) {
		DBUtilsImpl dbUtils = new DBUtilsImpl() ;
		
		String query = "INSERT INTO mobis_schema.calendar (" +
				"service_gtfs_id, " +
				"monday, " +
				"tuesday, " +
				"wednesday, " +
				"thursday, " +
				"friday, " +
				"saturday, " +
				"sunday, " +
				"start_date, " +
				"end_date) " +
				"VALUES ('" +
				calendar.getServiceId() + "'," + 
				calendar.getMonday() + "," + 
				calendar.getTuesday() + "," + 
				calendar.getWednesday() + "," + 
				calendar.getThursday() + "," + 
				calendar.getFriday() + "," + 
				calendar.getSaturday() + "," + 
				calendar.getSunday() + ",'" + 
				calendar.getStartDate() + "','" + 
				calendar.getEndDate() + "');" ; 
		
		return dbUtils.modifyDB(conn, query) ;
	}
	
	public int insertAllFieldsCalendarDate(Connection conn, CalendarDate calendarDate) {
		DBUtilsImpl dbUtils = new DBUtilsImpl() ;
		
		String query = "INSERT INTO mobis_schema.calendar_date (" +
				"service_gtfs_id, " +
				"date, " +
				"exception_type) " +
				"VALUES ('" +
				calendarDate.getServiceId() + "','" + 
				calendarDate.getDate() + "'," + 
				calendarDate.getExceptionType() + ");" ; 
		
		return dbUtils.modifyDB(conn, query) ;
	}
	
	public int insertRequiredFieldsTransfer(Connection conn, Transfer transfer) {
		DBUtilsImpl dbUtils = new DBUtilsImpl() ;
		
		String query = "INSERT INTO mobis_schema.transfer (" +
				"transfer_type, " +
				"from_stop_gtfs_id, " +
				"to_stop_gtfs_id) " +
				"VALUES (" +
				transfer.getTransferType() + "," + 
				transfer.getFromStopId() + "," +
				transfer.getToStopId() + ");" ; 
		
		return dbUtils.modifyDB(conn, query) ;
	} 
	
	public int insertAllFieldsTransfer(Connection conn, Transfer transfer) {
		DBUtilsImpl dbUtils = new DBUtilsImpl() ;
		
		String query = "INSERT INTO mobis_schema.transfer (" +
				"transfer_type, " +
				"min_transfer_time, " +
				"from_stop_gtfs_id, " +
				"to_stop_gtfs_id) " +
				"VALUES (" +
				transfer.getTransferType() + "," + 
				transfer.getMinTransferTime() + "," +
				transfer.getFromStopId() + "," +
				transfer.getToStopId() + ");" ; 
		
		return dbUtils.modifyDB(conn, query) ;
	}

	public int insertRequiredFieldsFeedInfo(Connection conn, FeedInfo feed) {
		DBUtilsImpl dbUtils = new DBUtilsImpl() ;
		
		String query = "INSERT INTO mobis_schema.feed_info (" +
				"feed_publisher_name, " +
				"feed_publisher_url, " +
				"feed_lang) " +
				"VALUES ('" +
				feed.getFeedPublisherName() + "','" + 
				feed.getFeedPublisherUrl() + "','" +
				feed.getFeedLang() + "');" ;  
		
		return dbUtils.modifyDB(conn, query) ;
	} 
	
	public int insertAllFieldsFeedInfo(Connection conn, FeedInfo feed) {
		DBUtilsImpl dbUtils = new DBUtilsImpl() ;
		
		String query = "INSERT INTO mobis_schema.feed_info (" +
				"feed_publisher_name, " +
				"feed_publisher_url, " +
				"feed_lang, " +
				"feed_start_date, " +
				"feed_end_date, " +
				"feed_version) " +
				"VALUES ('" +
				feed.getFeedPublisherName() + "','" + 
				feed.getFeedPublisherUrl() + "','" +
				feed.getFeedLang() + "'," +
				feed.getFeedStartDate() + "," + 
				feed.getFeedEndDate() + ",'" + 
				feed.getFeedVersion() + "');" ; 
		
		return dbUtils.modifyDB(conn, query) ;
	}

}
